package com.levy.excel;

import java.io.File;
import java.io.FileWriter;
import java.io.PrintWriter;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

/**
 * Demonstrates how to read excel styles for cells
 */
public class CellStyleDetails {
	
	
	
	
	public static void main(String[] args) throws Exception {
		
		int cityColumn = -1;
		int opColumn = -1;
		int msisdnColumn = -1;
		String msisdn="";
		String city="";
		String op = "";
		boolean f = false;
		
		List<String> ops = Arrays.asList(new String[]{"AIRCEL","VODAFONE ESSAR","BHARTI AIRTEL"});		
		Map<String,String> circleMap = new HashMap<String,String>();
		
		
		String file = "/home/levy.xia/Downloads/CircleTableMapping-FrowInternet-11June2013.xlsx";
		String outFile = "/home/levy.xia/out";
		
		Workbook wb = WorkbookFactory.create(new File(file));
		
		for (int sn = 0; sn < wb.getNumberOfSheets(); sn++) {
			Sheet sheet = wb.getSheetAt(sn);
//			System.out.println("Sheet #" + sn + " : " + sheet.getSheetName());

			for (Row row : sheet) {				
//				if (row.getRowNum() > 10)
//					break;
//				System.out.println("  Row " + row.getRowNum());

				for (Cell cell : row) {
//					System.out.print(" (" + cell.getColumnIndex() + ") ");
					
					int type = cell.getCellType();
					String value = null;
					
					switch (type) {
					case Cell.CELL_TYPE_BLANK:
					case Cell.CELL_TYPE_BOOLEAN:
					case Cell.CELL_TYPE_ERROR:
						break;
					case Cell.CELL_TYPE_NUMERIC:
						value = String.valueOf(cell.getNumericCellValue());
						break;
					case Cell.CELL_TYPE_STRING:
						value = cell.getStringCellValue();
						break;
					default:
						break;
					}

					if (cityColumn == -1 && value != null && value.equalsIgnoreCase("City")){
						cityColumn = cell.getColumnIndex();
					}
					if (opColumn == -1 && value != null && value.equalsIgnoreCase("Operators")){
						opColumn = cell.getColumnIndex();
					}
					if (msisdnColumn == -1 && value != null && value.equalsIgnoreCase("MSC CODES")){
						msisdnColumn = cell.getColumnIndex();
					}
					if (cityColumn == cell.getColumnIndex() && value != null){
//						System.out.println("A new city create");
						city = value;						
					}
					if (opColumn == cell.getColumnIndex() && value != null){
						op = value.trim().toUpperCase();			
					}
					
					if (msisdnColumn == cell.getColumnIndex() && value != null && ops.contains(op)) {
//						System.out.println("A msisdn number");
						value = convertExcelFormat(value);
						msisdn = msisdn.concat(value);
						if (!city.isEmpty()) {
							String old = circleMap.get(city);
							if (old == null) {
								circleMap.put(city, value);
							} else {
								circleMap.put(city, old.concat(value));
							}
						}
					}
					
//					System.out.println();
				}
			}
//			System.out.println();
		}
		PrintWriter out = new PrintWriter(new FileWriter(outFile));				
		for (String key : circleMap.keySet()){	
//			out.write(key + "," + circleMap.get(key));
			out.print(key);
			out.println(circleMap.get(key));
		}
		out.close();
	}
	
	private static String convertExcelFormat(String excel) throws Exception {
		StringBuffer joinedMsisdn = new StringBuffer();
		String[] group = null;
		excel = excel.replaceAll(",", " ").replaceAll("[ ]*-[ ]*", "-");
		group = excel.split("[ ]+");
		
//		System.out.println("old"+excel);
		for (String s : group) {
			String[] msisdns = s.trim().split("[ ]*-[ ]*");
			if (msisdns.length == 0) {
				System.out.println("There is a extra comma(,) in excle file");
			}
			if (msisdns.length == 1) {
				if (msisdns[0].length() >= 5){
					joinedMsisdn.append(",91" + msisdns[0]);
				}				
			} else if (msisdns.length == 2) {
				int begin = Integer.valueOf(msisdns[0]);
				int end = Integer.valueOf(msisdns[1]);
				if (begin > end){
					int tmp = begin;
					begin = end;
					end = tmp;
				}
				for (; begin <= end; begin++) {
					joinedMsisdn.append(",91" + begin);
				}				
			} else{
				throw new Exception("Excel format error! Msisdn arrange should be 9xxxx-9xxxx!");
			}
		}
		
		
//		System.out.println("new"+joinedMsisdn.toString());
		return joinedMsisdn.toString();
	}
}